﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using EmoneyWeiXinCommon;
using EmoneyWeiXinEntity;
using System.Data.SqlClient;
namespace EmoneyWeiXinDAL
{
    public class weixinUserDAL
    {
        public List<StockDoctorloguserEntity> getStockdoctordUser(string weixinid)
        {
            //string sql = "SELECT DISTINCT(openid) FromUserName FROM WXUserBLK WITH(NOLOCK) WHERE ISNULL(openid,'')<>'' AND weixinUserid=" + weixinid;
            string sql = "SELECT DISTINCT(openid) FromUserName FROM HYUserStockDoctor WITH(NOLOCK) WHERE ISNULL(openid,'')<>'' AND weixinid=" + weixinid;
            return SQLHelper.GetDataByColumnsLst<StockDoctorloguserEntity>(Config.EmoneyWeiXin, sql, null);
        }

        public List<StockDoctorloguserEntity> GetGroupUserAt48HHy(string weixinid, string groupId)
        {
            string sql = @" SELECT DISTINCT(T1.openid) FromUserName FROM
                           HYUserStockDoctor T1 WITH(NOLOCK) 
                           INNER JOIN UserGroupRel T2 WITH(NOLOCK)  ON T1.openid=T2.openId
                           WHERE ISNULL(T1.openid,'')<>'' AND T2.groupId=" + groupId + "  AND T1.weixinid=" + weixinid;
            return SQLHelper.GetDataByColumnsLst<StockDoctorloguserEntity>(Config.EmoneyWeiXin, sql, null);
        }

        public List<weixinUserEntity> getWeixinUser1()
        {
            string sql = "SELECT * FROM weixinUser WITH(NOLOCK) WHERE isvalid=1";
            return SQLHelper.GetDataByColumnsLst<weixinUserEntity>(Config.EmoneyWeiXin, sql, null);
        }
        public List<weixinUserEntity> getWeixinUser1(int userid)
        {
            SqlParameter[] parameters = new SqlParameter[]{
            new SqlParameter("@userid",userid)
            };
            return SQLHelper.GetProcedureDataByColumnsLst<weixinUserEntity>(Config.EmoneyWeiXin, "sp_users_weixinUser_SelectByUserID", parameters);
        }

        public List<weixinUserEntity> GetWeixinUser(string id, int pageindex = 0, int pagecount = 0)
        {
            string sql = "";
            SQLTools tools = new SQLTools();
            if (string.IsNullOrEmpty(id))
            {
                int startnum = (pageindex - 1) * pagecount + 1;
                int endnum = pageindex * pagecount;
                sql = "SELECT * FROM( select id, title, Author, AppId, AppSecret, Token, EncodingAESKey, servicesUrl, gzUrl, IsMicrocommunity, MicrocommunityUrl, createtime,createUser, updatetime,UpdateUser, isvalid, remark,weixinNumber,ROW_NUMBER() OVER(ORDER BY id desc) RowNums  from weixinUser with(nolock) ) T1 WHERE isvalid=1 and  RowNums BETWEEN " + startnum + " AND " + endnum + "";
            }
            else
            {
                sql = "select id, title, Author, AppId, AppSecret, Token, EncodingAESKey, servicesUrl, gzUrl, IsMicrocommunity, MicrocommunityUrl, createtime,createUser, updatetime,UpdateUser, isvalid, remark,weixinNumber from weixinUser with(nolock) where id=@id";
                tools.AddSqlParameter("id", SqlDbType.Int, 20, id);
            }
            return SQLHelper.GetDataByColumnsLst<weixinUserEntity>(Config.EmoneyWeiXin, sql, tools.GetSqlParameters());
        }

        public int GetTableNameCount(string tablename)
        {
            string sql = "";

            switch (tablename)
            {
                case "weixinUser":
                    sql = "select count(id) from weixinUser where isvalid=1";
                    break;
                default:

                    break;
            }

            if (!string.IsNullOrEmpty(sql))
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(Config.EmoneyWeiXin, CommandType.Text, sql, null));
            }
            return 0;
        }



        public int weixinUpdate(weixinUserEntity wetity)
        {
            string sql = "";
            SQLTools tools = new SQLTools();
            if (wetity.id > 0)
            {
                sql = "update weixinUser set title=@title, Author=@Author, AppId=@AppId, AppSecret=@AppSecret, Token=@Token, EncodingAESKey=@EncodingAESKey,servicesUrl=@servicesUrl, gzUrl=@gzUrl, IsMicrocommunity=@IsMicrocommunity, MicrocommunityUrl=@MicrocommunityUrl,updatetime=getdate(), UpdateUser=@UpdateUser,weixinNumber=@weixinNumber,remark=@remark where id=" + wetity.id;
            }
            else
            {
                sql = "insert into weixinUser(title, Author, AppId, AppSecret, Token, EncodingAESKey, servicesUrl, gzUrl, IsMicrocommunity, MicrocommunityUrl, createtime, updatetime, isvalid, UpdateUser, createUser,weixinNumber,remark) values("
                   + "@title, @Author, @AppId, @AppSecret, @Token, @EncodingAESKey, @servicesUrl, @gzUrl, @IsMicrocommunity, @MicrocommunityUrl, getdate(), getdate(), 1, @UpdateUser, @createUser,@weixinNumber,@remark)";
            }
            tools.AddSqlParameter("title", SqlDbType.VarChar, 50, wetity.title);
            tools.AddSqlParameter("Author", SqlDbType.VarChar, 50, wetity.Author);
            tools.AddSqlParameter("AppId", SqlDbType.VarChar, 200, wetity.AppId);
            tools.AddSqlParameter("AppSecret", SqlDbType.VarChar, 200, wetity.AppSecret);

            tools.AddSqlParameter("Token", SqlDbType.VarChar, 50, wetity.Token);
            tools.AddSqlParameter("EncodingAESKey", SqlDbType.VarChar, 50, wetity.EncodingAESKey);
            tools.AddSqlParameter("servicesUrl", SqlDbType.VarChar, 500, wetity.servicesUrl);
            tools.AddSqlParameter("gzUrl", SqlDbType.VarChar, 500, wetity.gzUrl);
            tools.AddSqlParameter("IsMicrocommunity", SqlDbType.VarChar, 20, wetity.IsMicrocommunity);
            tools.AddSqlParameter("MicrocommunityUrl", SqlDbType.VarChar, 500, wetity.MicrocommunityUrl);
            tools.AddSqlParameter("UpdateUser", SqlDbType.VarChar, 20, wetity.UpdateUser);
            tools.AddSqlParameter("createUser", SqlDbType.VarChar, 20, wetity.createUser);
            tools.AddSqlParameter("weixinNumber", SqlDbType.VarChar, 100, wetity.weixinNumber);
            tools.AddSqlParameter("remark", SqlDbType.VarChar, 500, wetity.remark);
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, tools.GetSqlParameters());
        }

        public int weixindel(string id)
        {
            string sql = "UPDATE weixinUser SET IsValid=CASE WHEN IsValid=1 THEN 0 ELSE 1 END where id=" + id;
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, null);
        }

        public void getStockdictor(string sid, string str, string weixinid, string fz = "")
        {
            if (!string.IsNullOrEmpty(fz))
            {
                if (fz == "dtw")
                {
                    fz = "多图文";
                }
                else if (fz == "voice")
                {
                    fz = "voice";
                }
                else if (fz == "付费")
                {
                    fz = "付费";
                }
                else
                {
                    fz = "群发";
                }
                SQLTools tools = new SQLTools();
                tools.AddSqlParameter("result", SqlDbType.VarChar, 100000, str);
                string sql = "INSERT INTO dbo.StockDoctorlog(weixinid, newsreportid, wType, FromUserName, ToUserName, CONTENT, eventkey, updatetime, Stock, result)";
                sql += " values(" + weixinid + ",0,10,'" + sid + "','','" + fz + "','',getdate(),'',@result)";
                SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, tools.GetSqlParameters());
            }
            else
            {
                if (!string.IsNullOrEmpty(sid) && !string.IsNullOrEmpty(str))
                {
                    string sql = "INSERT INTO dbo.StockDoctorlog(weixinid, newsreportid, wType, FromUserName, ToUserName, CONTENT, eventkey, updatetime, Stock, result)";
                    sql += " SELECT weixinid,newsreportid,10 wType,FromUserName,ToUserName,CONTENT,eventkey,GETDATE() updatetime,Stock,'" + str + "' result FROM StockDoctorlog WITH(NOLOCK) WHERE id='" + sid + "'";
                    //string sql = "update StockDoctorlog set result=result+'^^^" + str + "' where id=" + sid;
                    SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, null);
                }
            }
        }
    }
}
